# from random import choice
# from docx import Document
# from docx.enum.style import WD_STYLE_TYPE
# from docx.enum.table import WD_TABLE_ALIGNMENT
# from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
# from docx.shared import Cm
# from openpyxl import load_workbook
# from docx.shared import Pt
# from docx.oxml.ns import qn
#
# document = Document()
# document.styles['Normal'].font.name = u'宋体'
# document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
# document.styles['Normal'].font.size = Pt(22)

#
# # 将界址点表导入word
# def xlsx2docx(fn):
#     wb = load_workbook(fn,data_only=True)
#     document = Document()
#     document.styles['Normal'].font.name = u'宋体'
#     document.styles['Normal']._element.rPr.rFonts.set(qn('w:estAsia'), u'宋体')
#     document.styles['Normal'].font.size = Pt(22)
#
#     document.add_paragraph('    地块{0}由点号J1-J{1},J1围成')
#     document.add_paragraph('    占地面积：P={:.2f}平方米，合{}亩。')
#
#     # table_styles = [style for style in document.styles
#     #                 if style.type==WD_STYLE_TYPE.TABLE]
#
#     # 选用表格格式
#     table_styles = ['Table Grid']
#
#     # 写入界址点数据
#     for ws in wb.worksheets:
#         # 创建表头
#         rows = list(ws.rows)
#         #document.add_paragraph('地块'+ws.title[5]+'由点号')
#         document.add_paragraph('')
#         document.add_paragraph('    地块{0}由点号J1-J{1},J1围成'.format(ws.title[5],len(rows)-1))
#         document.add_paragraph('    占地面积：P={:.2f}平方米，合{}亩。'.format(float(ws.cell(2,4).value),ws.cell(2,5).value))
#         table = document.add_table(rows=len(rows),
#                                    cols=len(rows[0])-2,
#                                    style=choice(table_styles))
#
#         # for irow,row in enumerate(rows):
#         #     for icol,col in enumerate(row):
#         #         table.cell(irow,icol).text = str(col.value)
#                 #table.cell(irow, icol).text = format(str(col.value), '.3f')
#         # 写入数据
#         for table_col in range(1,4):
#             table.cell(0,table_col-1).text = str(ws.cell(1,table_col).value)
#         for table_row in range(2,len(rows)+1):
#             for table_col in range(2,4):
#                 table.cell(table_row-1,table_col-1).text = format(ws.cell(table_row,table_col).value, '.3f')
#         for table_row in range(2, len(rows) + 1):
#             table.cell(table_row - 1, 0).text = str(ws.cell(table_row,1).value)
#         for table_row in range(0,len(rows)):
#             for table_col in range(0,3):
#                 # 垂直居中
#                 table.cell(table_row,table_col).paragraphs[0].alignment = WD_TABLE_ALIGNMENT.CENTER
#                 table.cell(table_row,table_col).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
#         for row in table.rows:
#                 # 设置行高
#                 row.height = Cm(1)
#
#     document.save(fn[:-4]+'docx')
# xlsx2docx('test.xlsx')
#class JZDForm(object):
from random import choice
from docx import Document
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import Cm
from openpyxl import load_workbook
from docx.shared import Pt
from docx.oxml.ns import qn
import xlrd
import openpyxl
from openpyxl.styles import Border, Side, colors
from openpyxl.styles import Alignment
from openpyxl.styles import Font
from openpyxl import Workbook

def creat_JZDForm():
    face = []
    jlist = []
    pointx = []
    pointy = []
    # jlist_first = []
    # pointx_first = []
    # pointy_first = []
    rowlist = []
    mj = []
    mjm = []
    file = '大国工匠违法套图1_JZD.xls'
    print('显示')
    def readJZD():

        print('查询')
        workbook = xlrd.open_workbook(file)  # 创建工作空间
        sheet1 = workbook.sheet_by_index(0)  # 选取第0个表

        mj1 = (sheet1.col_values(1))
        mj1.pop(0)

        mjm1 = (sheet1.col_values(2))
        mjm1.pop(0)

        face1 = (sheet1.col_values((3)))
        # 把第一行标题弹出去
        face1.pop(0)

        jlist1 = (sheet1.col_values(4))
        jlist1.pop(0)

        pointx1 = (sheet1.col_values((5)))
        pointx1.pop(0)

        pointy1 = (sheet1.col_values((6)))
        pointy1.pop(0)

        # 添加数据
        n1 = len(face1)
        for a1 in range(0, n1):
            face.append(int(face1[a1]))

        n2 = len(jlist1)
        for a2 in range(0, n2):
            jlist.append(jlist1[a2])
            rowlist.append(int(jlist1[a2][1:]))

        n3 = len(pointx1)
        for a3 in range(0, n3):
            pointx.append(pointx1[a3])

        n4 = len(pointy1)
        for a4 in range(0, n4):
            pointy.append(pointy1[a4])

        n5 = len(mj1)
        for a5 in range(0, n5):
            mj.append(mj1[a5])

        n6 = len(mjm1)
        for a6 in range(0, n6):
            mjm.append(mjm1[a6])

        # i = 0
        # for e in jlist1:
        #     if e == 'J1':
        #         jlist_first.append(e)
        #         pointx_first.append(pointx1[i])
        #         pointy_first.append(pointy1[i])
        #     i = i + 1

    def writeJZD():
        wb = Workbook()

        # 边框设置
        border_set = Border(left=Side(style='medium', color=colors.BLACK),
                            right=Side(style='medium', color=colors.BLACK),
                            top=Side(style='medium', color=colors.BLACK),
                            bottom=Side(style='medium', color=colors.BLACK))
        # 字体设置
        font1 = Font(name=u'Calibri', bold=False, italic=False, size=10.5)

        n = 0
        ws = locals()
        for i in jlist:

            # 通过J1判断分别写入表
            if i == 'J1':
                ws['ws' + str(face[n])] = wb.create_sheet(index=face[n], title='Sheet' + str(face[n]))
                # 写入表头和样式
                ws['ws' + str(face[n])].cell(row=1, column=1).value = '点号'
                ws['ws' + str(face[n])].cell(row=1, column=2).value = 'X坐标'
                ws['ws' + str(face[n])].cell(row=1, column=3).value = 'Y坐标'
                ws['ws' + str(face[n])].cell(row=1, column=4).value = '面积'
                ws['ws' + str(face[n])].cell(row=1, column=5).value = '面积亩'

                ws['ws' + str(face[n])].column_dimensions['B'].width = 20
                ws['ws' + str(face[n])].column_dimensions['C'].width = 20
                ws['ws' + str(face[n])].column_dimensions['D'].width = 20
                ws['ws' + str(face[n])].column_dimensions['E'].width = 20

                for i1 in range(1, 6):
                    ws['ws' + str(face[n])].cell(row=1, column=i1).border = border_set
                    ws['ws' + str(face[n])].cell(row=1, column=i1).font = font1
                    ws['ws' + str(face[n])].cell(row=1, column=i1).alignment = Alignment(horizontal='center',
                                                                                              vertical='center',
                                                                                              text_rotation=0,
                                                                                              wrap_text=True,
                                                                                              shrink_to_fit=False,
                                                                                              indent=0)
                # wb.save('test.xlsx')
            n = n + 1

        # 写入数据和样式
        n1 = 0
        for j in face:

            # 确定表，并在每个表写入数据
            if j:
                sheet1 = wb['Sheet{0}'.format(j)]
                sheet1.cell(row=int(rowlist[n1]) + 1, column=1).value = jlist[n1]
                sheet1.cell(row=int(rowlist[n1]) + 1, column=2).value = pointy[n1]
                sheet1.cell(row=int(rowlist[n1]) + 1, column=3).value = pointx[n1]
                sheet1.cell(row=int(rowlist[n1]) + 1, column=4).value = mj[n1]
                sheet1.cell(row=int(rowlist[n1]) + 1, column=5).value = mjm[n1]

                wb.save('test.xlsx')

                for j1 in range(1, 6):
                    # 设置插入行的单元格格式
                    sheet1.cell(row=int(rowlist[n1]) + 1, column=j1).border = border_set
                    # 设置单元格字体
                    sheet1.cell(row=int(rowlist[n1]) + 1, column=j1).font = font1
                    # 设置插入行的单元格数字格式为保留三位小数
                    sheet1.cell(row=int(rowlist[n1]) + 1, column=j1).number_format = '0.000'
                    # 单元格居中
                    sheet1.cell(row=int(rowlist[n1]) + 1, column=j1).alignment = Alignment(horizontal='center',
                                                                                                vertical='center',
                                                                                                text_rotation=0,
                                                                                                wrap_text=True,
                                                                                                shrink_to_fit=False,
                                                                                                indent=0)

            n1 = n1 + 1
        # 遍历所有表，修改最后J1
        sheets = wb.sheetnames
        for j1 in range(1, len(sheets)):
            sheet = wb[sheets[j1]]
            sheet.cell(row=sheet.max_row, column=1).value = 'J1'

        # 删除多余表
        ws = wb['Sheet']
        wb.remove(ws)
        wb.save('test.xlsx')

    if __name__ == '__main__':
        readJZD()
        writeJZD()
creat_JZDForm()